package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Sort;


public class SortDao {

	 Connection conn=null;

	public SortDao(Connection conn) {
		super();
		this.conn = conn;
	}
	 
	
	public  int create( Sort index ) throws SQLException{	
		int result=0;
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "insert  into sort (name,display,supplier) "
	        			+ "values (?,?,?)";
	        	pstm=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
	        	pstm.setString(1, index.getName());
	        	pstm.setInt(2, index.getDisplay());
	        	pstm.setString(3, index.getSupplier());        
				pstm.executeUpdate();
				
				rs=pstm.getGeneratedKeys();
				rs.next();
				result=rs.getInt(1);
		
				
		   		
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return result;
	}
	
	public  void delete(Sort index) throws SQLException{	
		
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "delete  from sort where id="+index.getId()+"";		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stm.executeUpdate(sql);
				
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		
	}
	
	public  int update( Sort index ) throws SQLException{	
		int result=0;
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "update sort set name=?,display=? where id = ?";
	        	pstm=conn.prepareStatement(sql);	        	
	        	pstm.setString(1, index.getName());
	        	pstm.setInt(2, index.getDisplay());
	        	pstm.setString(3, index.getSupplier());      
				result=pstm.executeUpdate();
				
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return result;
	}
	
	

	public List< Sort> search( String sql) throws SQLException{	
		List< Sort> indexes= new ArrayList<Sort>();
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
	
	        try {
	        		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
				rs=stm.executeQuery(sql);
				while(rs.next()){
					Sort index = new Sort();
					index.setId(rs.getInt("id"));
					index.setName(rs.getString("name"));
					index.setSupplier(rs.getString("supplier"));
					index.setDisplay(rs.getInt("display"));
					indexes.add(index);
				}
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return indexes;
	}	
	
	 public Sort selectById(Sort index) throws Exception{
		   String sql="select * from sort  where id = "+ index.getId()+" ";
		   List<Sort> indexes= search(sql);
		   Sort indexExist = null;
		   if(indexes.size()>0){ indexExist= indexes.get(0); 
		   }
		   return indexExist;
	   }
	
	 public static  String  getSearchSql(Sort sort){
			String sql="select * from sort";
			int count=0;
			if(sort.getId()!=0){ sql="select * from sort  where id = "+sort.getId()+""; return sql;
			 }else{
				
				if(!sort.getSupplier().isEmpty()) {	
					if(count==0) {sql= sql+" where supplier = "+sort.getSupplier();
					}else{
						sql= sql+" and  supplier = "+sort.getSupplier();
					  }  
					  count++; }
				
			}
			return sql;
		}
}
